﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Xml.Linq;

namespace FortnoxAPI
{
    /// <summary>
    /// Get VoucherList from Fortnox
    /// </summary>
    public partial class GetVoucherList
    {

        /// <summary>
        /// Gets the values.
        /// </summary>
        /// <param name="url">The URL.</param>
        /// <param name="token">The token.</param>
        /// <param name="db">The db.</param>
        /// <param name="Series">The series.</param>
        /// <param name="year">The year.</param>
        /// <param name="fromid">The fromid.</param>
        /// <param name="toid">The toid.</param>
        /// <returns></returns>
        public static DataTable GetValues(string url, string token, string db, string Series, string year, string fromid, string toid)
        {

            XDocument doc = XDocument.Load(url + "token=" + token + "&db=" + db + "&series=" + Series + "&year=" + year + "&fromid=" + fromid + "&toid=" + toid);

            DataTable dt1 = new DataTable();
            dt1.Columns.Add("VoucherId", typeof(string));
            dt1.Columns.Add("VoucherRdate", typeof(string));
            dt1.Columns.Add("VoucherCreatedby", typeof(string));
            dt1.Columns.Add("VoucherCreated", typeof(string));
            dt1.Columns.Add("VoucherCurunit", typeof(string));
            dt1.Columns.Add("VoucherCurrate", typeof(string));
            dt1.Columns.Add("VoucherCurrency", typeof(string));
            dt1.Columns.Add("VoucherRef", typeof(string));
            dt1.Columns.Add("VoucherTdate", typeof(string));
            dt1.Columns.Add("VoucherDescr", typeof(string));
            dt1.Columns.Add("VoucherModifiedby", typeof(string));
            dt1.Columns.Add("VoucherModified", typeof(string));


            DataTable dt2 = new DataTable();
            dt2.Columns.Add("PostId", typeof(string));
            dt2.Columns.Add("PostDescr", typeof(string));
            dt2.Columns.Add("PostRowno", typeof(string));
            dt2.Columns.Add("PostCre", typeof(string));
            dt2.Columns.Add("PostDeb", typeof(string));
            dt2.Columns.Add("PostAccount", typeof(string));
            dt2.Columns.Add("PostCc", typeof(string));
            dt2.Columns.Add("PostTransinfo", typeof(string));


            DataTable dt3 = new DataTable();
            dt3.Columns.Add("VoucherId", typeof(string));
            dt3.Columns.Add("VoucherRdate", typeof(string));
            dt3.Columns.Add("VoucherCreatedby", typeof(string));
            dt3.Columns.Add("VoucherCreated", typeof(string));
            dt3.Columns.Add("VoucherCurunit", typeof(string));
            dt3.Columns.Add("VoucherCurrate", typeof(string));
            dt3.Columns.Add("VoucherCurrency", typeof(string));
            dt3.Columns.Add("VoucherRef", typeof(string));
            dt3.Columns.Add("VoucherTdate", typeof(string));
            dt3.Columns.Add("VoucherDescr", typeof(string));
            dt3.Columns.Add("VoucherModifiedby", typeof(string));
            dt3.Columns.Add("VoucherModified", typeof(string));

            dt3.Columns.Add("PostId", typeof(string));
            dt3.Columns.Add("PostDescr", typeof(string));
            dt3.Columns.Add("PostRowno", typeof(string));
            dt3.Columns.Add("PostCre", typeof(string));
            dt3.Columns.Add("PostDeb", typeof(string));
            dt3.Columns.Add("PostAccount", typeof(string));
            dt3.Columns.Add("PostCc", typeof(string));
            dt3.Columns.Add("PostTransinfo", typeof(string));


            IEnumerable<XElement> Posts = from post in doc.Descendants("post")

                                          //where (string)post.Attribute("id").Value == "1"
                                          select post;


            foreach (XElement Post in Posts)
            {
                DataRow dr2 = dt2.NewRow();

                if (Post.Attribute("id") != null)
                {
                    dr2["PostId"] = Post.Attribute("id").Value;
                }
                else
                {
                    dr2["PostId"] = "";
                }

                if (Post.Attribute("descr") != null)
                {
                    dr2["PostDescr"] = Post.Attribute("descr").Value;
                }
                else
                {
                    dr2["PostDescr"] = "";
                }


                if (Post.Attribute("rowno") != null)
                {
                    dr2["PostRowno"] = Post.Attribute("rowno").Value;
                }
                else
                {
                    dr2["PostRowno"] = "";
                }

                if (Post.Attribute("cre") != null)
                {
                    dr2["PostCre"] = Post.Attribute("cre").Value;
                }
                else
                {
                    dr2["PostCre"] = 0;
                }

                if (Post.Attribute("deb") != null)
                {
                    dr2["PostDeb"] = Post.Attribute("deb").Value;
                }
                else
                {
                    dr2["PostDeb"] = 0;
                }

                if (Post.Attribute("account") != null)
                {
                    dr2["PostAccount"] = Post.Attribute("account").Value;
                }
                else
                {
                    dr2["PostAccount"] = "";
                }

                if (Post.Attribute("cc") != null)
                {
                    dr2["PostCc"] = Post.Attribute("cc").Value;
                }
                else
                {
                    dr2["PostCc"] = "0";
                }

                if (Post.Attribute("transinfo") != null)
                {
                    dr2["PostTransinfo"] = Post.Attribute("transinfo").Value;
                }
                else
                {
                    dr2["PostTransinfo"] = "";
                }
                dt2.Rows.Add(dr2);
            }



            IEnumerable<XElement> Vouchers = from voucher in doc.Descendants("voucher")

                                             //where (string)voucher.Attribute("id").Value == "1"
                                             select voucher;


            foreach (XElement Voucher in Vouchers)
            {
                DataRow dr1 = dt1.NewRow();

                if (Voucher.Attribute("id") != null)
                {
                    dr1["VoucherId"] = Voucher.Attribute("id").Value;
                }
                else
                {
                    dr1["VoucherId"] = "";
                }

                if (Voucher.Attribute("rdate") != null)
                {
                    dr1["VoucherRdate"] = Voucher.Attribute("rdate").Value;
                }
                else
                {
                    dr1["VoucherRdate"] = "";
                }

                if (Voucher.Attribute("createdby") != null)
                {
                    dr1["VoucherCreatedby"] = Voucher.Attribute("createdby").Value;
                }
                else
                {
                    dr1["VoucherCreatedby"] = "";
                }

                if (Voucher.Attribute("created") != null)
                {
                    dr1["VoucherCreated"] = Voucher.Attribute("created").Value;
                }
                else
                {
                    dr1["VoucherCreated"] = "";
                }

                if (Voucher.Attribute("curunit") != null)
                {
                    dr1["VoucherCurunit"] = Voucher.Attribute("curunit").Value;
                }
                else
                {
                    dr1["VoucherCurunit"] = "";
                }

                if (Voucher.Attribute("currate") != null)
                {
                    dr1["VoucherCurrate"] = Voucher.Attribute("currate").Value;
                }
                else
                {
                    dr1["VoucherCurrate"] = "";
                }

                if (Voucher.Attribute("currency") != null)
                {
                    dr1["VoucherCurrency"] = Voucher.Attribute("currency").Value;
                }
                else
                {
                    dr1["VoucherCurrency"] = "";
                }

                if (Voucher.Attribute("ref") != null)
                {
                    dr1["VoucherRef"] = Voucher.Attribute("ref").Value;
                }
                else
                {
                    dr1["VoucherRef"] = "";
                }

                if (Voucher.Attribute("tdate") != null)
                {
                    dr1["VoucherTdate"] = Voucher.Attribute("tdate").Value;
                }
                else
                {
                    dr1["VoucherTdate"] = "";
                }

                if (Voucher.Attribute("descr") != null)
                {
                    dr1["VoucherDescr"] = Voucher.Attribute("descr").Value;
                }
                else
                {
                    dr1["VoucherDescr"] = "";
                }

                if (Voucher.Attribute("modifiedby") != null)
                {
                    dr1["VoucherModifiedby"] = Voucher.Attribute("modifiedby").Value;
                }
                else
                {
                    dr1["VoucherModifiedby"] = "";
                }

                if (Voucher.Attribute("modified") != null)
                {
                    dr1["VoucherModified"] = Voucher.Attribute("modified").Value;
                }
                else
                {
                    dr1["VoucherModified"] = "";
                }

                dt1.Rows.Add(dr1);
            }

            var results = from table1 in dt1.AsEnumerable()
                          join table2 in dt2.AsEnumerable() on (string)table1["VoucherId"] equals (string)table2["PostId"]
                          orderby (string)table1["VoucherId"]
                          select new
                          {
                          
                            
                              VoucherId = (string)table1["VoucherId"],
                              VoucherRdate = (string)table1["VoucherRdate"],
                              VoucherCreatedby = (string)table1["VoucherCreatedby"],
                              VoucherCreated = (string)table1["VoucherCreated"],
                              VoucherCurunit = (string)table1["VoucherCurunit"],
                              VoucherCurrate = (string)table1["VoucherCurrate"],
                              VoucherCurrency = (string)table1["VoucherCurrency"],
                              VoucherRef = (string)table1["VoucherRef"],
                              VoucherTdate = (string)table1["VoucherTdate"],
                              VoucherDescr = (string)table1["VoucherDescr"],
                              VoucherModifiedby = (string)table1["VoucherModifiedby"],
                              VoucherModified = (string)table1["VoucherModified"],

                              PostId = (string)table2["PostId"],
                              PostDescr = (string)table2["PostDescr"],
                              PostRowno = (string)table2["PostRowno"],
                              PostCre = (string)table2["PostCre"],
                              PostDeb = (string)table2["PostDeb"],
                              PostAccount = (string)table2["PostAccount"],
                              PostCc = (string)table2["PostCc"],
                              PostTransinfo = (string)table2["PostTransinfo"]

                          };
            foreach (var item in results)
            {
                DataRow dr3 = dt3.NewRow();
                dr3["VoucherId"] = item.VoucherId;
                dr3["VoucherRdate"] = item.VoucherRdate;
                dr3["VoucherCreatedby"] = item.VoucherCreatedby;
                dr3["VoucherCreated"] = item.VoucherCreated;
                dr3["VoucherCurunit"] = item.VoucherCurunit;
                dr3["VoucherCurrate"] = item.VoucherCurrate;
                dr3["VoucherCurrency"] = item.VoucherCurrency;
                dr3["VoucherRef"] = item.VoucherRef;
                dr3["VoucherTdate"] = item.VoucherTdate;
                dr3["VoucherDescr"] = item.VoucherDescr;
                dr3["VoucherModifiedby"] = item.VoucherModifiedby;
                dr3["VoucherModified"] = item.VoucherModified;

                dr3["PostId"] = item.PostId;
                dr3["PostDescr"] = item.PostDescr;
                dr3["PostRowno"] = item.PostRowno;
                dr3["PostCre"] = item.PostCre;
                dr3["PostDeb"] = item.PostDeb;
                dr3["PostAccount"] = item.PostAccount;
                dr3["PostCc"] = item.PostCc;
                dr3["PostTransinfo"] = item.PostTransinfo;

                dt3.Rows.Add(dr3);

            }

            return dt3;
        }

    }
}
